import json
from fastapi import Depends, APIRouter
from pydantic import BaseModel

from routes.slot_management import sqlpyodbc,sql_polars
from util.JWT import get_current_user


upkeep_slot = APIRouter()

# 新增货位

class addloctionType(BaseModel):
    LocationID: str
    MaxPallets: int
    Note: str=""

@upkeep_slot.post("/addloction")
async def addloction(data: addloctionType, id: dict = Depends(get_current_user)):
    sql=f"INSERT INTO StorageLocation(LocationID,MaxPallets,Note) VALUES('{data.LocationID}',{data.MaxPallets},'{data.Note}')"
    try:
        if sqlpyodbc.execute(sql)>0:
            return {"code": 200, "status": "ok", "msg": "新增成功"}
        return {"code": 400, "status": "no", "msg": "新增失败"}
    except:
        return {"code": 400, "status": "no", "msg": "error"}
# 修改货位
class updateType(BaseModel):
    LocationID: str
    MaxPallets: int
    Note: str=""
@upkeep_slot.post("/updateloction")
async def updateloction(data: updateType, id: dict = Depends(get_current_user)):
    sql=f"UPDATE StorageLocation SET MaxPallets={data.MaxPallets},Note='{data.Note}' WHERE LocationID='{data.LocationID}'"
    try:
        if sqlpyodbc.execute(sql)>0:
            return {"code": 200, "status": "ok", "msg": "修改成功"}
        return {"code": 400, "status": "no", "msg": "修改失败"}
    except:
        return {"code": 400, "status": "no", "msg": "error"}

class etLocationType(BaseModel):
    locationID: str
@upkeep_slot.post("/getLocation")
async def getLocation(data:etLocationType,id: dict = Depends(get_current_user)):
    sql=""
    if data.locationID == "":
        sql = """
        SELECT 
        sl.LocationID,
        sl.MaxPallets,
        ISNULL(sl.MaxPallets, 0) - ISNULL(cr.use_qty, 0) AS RemCap,
        sl.Note
    FROM 
        StorageLocation sl
    LEFT JOIN 
        (SELECT 
            LocationID,
            COUNT(LocationID) AS use_qty 
         FROM 
            CaseResult 
         WHERE 
            isout = 0 AND LocationID != ''
         GROUP BY 
            LocationID
        ) cr ON sl.LocationID = cr.LocationID
        """
    else:
        sql = f"""
                SELECT 
                sl.LocationID,
                sl.MaxPallets,
                ISNULL(sl.MaxPallets, 0) - ISNULL(cr.use_qty, 0) AS RemCap,
                sl.Note
            FROM 
                StorageLocation sl
            LEFT JOIN 
                (SELECT 
                    LocationID,
                    COUNT(LocationID) AS use_qty 
                 FROM 
                    CaseResult 
                 WHERE 
                    isout = 0 AND LocationID != ''
                 GROUP BY 
                    LocationID
                ) cr ON sl.LocationID = cr.LocationID WHERE sl.LocationID='{data.locationID}'
                """

    df = sql_polars.query(sql)
    if df.height < 1:
        return {
            "code": 204,
            "status": "no",
            "msg": "没有内容",
            "data": []
        }
    df = df.to_dicts()
    json_string = json.dumps(df, ensure_ascii=False)
    return {
        "code": 200,
        "status": "ok",
        "msg": "success",
        "data": json_string
    }



